'''
Created on Apr 27, 2013

@author: priya
'''
import sqlite3 as lite
import sys
import ucsv as csv 

csvDir = 'C:\\Users\\priya\\Workspace_2012-07-28\\KDDcup\\DataAsCsvFiles\\'

def createStatementsFile(name):
    csvFile = csvDir + name + '.csv'
    csvHdl = open(csvFile)
    csvRdr = csv.reader(csvHdl) #this is an iterable. don't use it as list
    
    opfile = csvDir + name + '.sql'
    
    opHdl = open(opfile,"w")
    
    rownum = 0
    try: 
        opHdl.write("BEGIN TRANSACTION;\n")      
        dropStatement = 'DROP TABLE IF EXISTS ' + name + ';\n'
        opHdl.write(dropStatement)
        
        headerTypes = []
        
        opStr = ""
        
        for row in csvRdr:
            # Save header row.
            if rownum == 0:
                header = row
                createStatement = "CREATE TABLE " + name + '('# (Id INT, Name TEXT, Price INT)
                for title in header: 
                    if title[-2:] == 'Id' or title == 'Year': #arbitrary rule just for this dataset. if last two letters are ID, it's an int. saves space.
                        headerTypes.append('INT')
                    else: 
                        headerTypes.append('TEXT')
                    createStatement = createStatement + title + " " + headerTypes[-1] + ', '
                createStatement = createStatement[:-2]
                createStatement = createStatement + ");\n" 
                
                opHdl.write(createStatement)
                
            else:
                colnum = 0
                insertStatement = "INSERT INTO " + name + " VALUES("
                for col in row:
                    #sanitize string
                    colStr = unicode(col)
                    colStr = colStr.replace("'","")
                    if headerTypes[colnum] == 'TEXT':
                        colStr = "'" + colStr + "'"
                        
                    insertStatement = insertStatement + colStr + ','
                    colnum += 1
                insertStatement = insertStatement[:-1];
                insertStatement = insertStatement + ");"
                
                opStr = opStr + unicode(insertStatement) + "\n"               
                
                if (rownum%2 == 0):
                    opHdl.write(opStr.encode('utf-8'))
                    opStr = ""
                if rownum%1000 == 0: 
                    print(rownum)
                
            rownum += 1
        #insert commit statement
        opHdl.write(opStr)
        opHdl.close()
    except: 
        print(sys.exc_info())
        print insertStatement
        sys.exit(1)

def checkTables():
    
    csvFile = csvDir + 'Author' + '.csv'
    csvHdl = open(csvFile)
    
    con = lite.connect('Author.db')
    count = 0
    
    with con:
        cur = con.cursor()   
        cur.execute("SELECT * FROM Author")
        while True:
            row = cur.fetchone()
            count = count+1
            if row == None:
                break
            print row
    print(len(csvHdl.readlines()))
    print count
        
        
def insertCsvFile(name):
    csvFile = csvDir + name + '.csv'
    csvHdl = open(csvFile)
    csvRdr = csv.reader(csvHdl) #this is an iterable. don't use it as list
    
    rownum = 0
    try:
        con = lite.connect(name + '.db')
        cur = con.cursor() 
        
        dropStatement = 'DROP TABLE IF EXISTS ' + name + ';'
        cur.executescript(dropStatement)
        
        headerTypes = []
        
        for row in csvRdr:
            # Save header row.
            if rownum == 0:
                header = row
                createStatement = "CREATE TABLE " + name + '('# (Id INT, Name TEXT, Price INT)
                for title in header: 
                    if title[-2:] == 'Id' or title == 'Year': #arbitrary rule just for this dataset. if last two letters are ID, it's an int. saves space.
                        headerTypes.append('INT')
                    else: 
                        headerTypes.append('TEXT')
                    createStatement = createStatement + title + " " + headerTypes[-1] + ', '
                createStatement = createStatement[:-2]
                createStatement = createStatement + ");"
                cur.executescript(createStatement)
            else:
                colnum = 0
                insertStatement = "INSERT INTO " + name + " VALUES("
                for col in row:
                    #sanitize string
                    colStr = unicode(col)
                    colStr = colStr.replace("'","")
                    if headerTypes[colnum] == 'TEXT':
                        colStr = "'" + colStr + "'"
                        
                    insertStatement = insertStatement + colStr + ','
                    try:
                        pass#print(u'%-8s: %s' % (header[colnum], col))
                    except:
                        print(header[colnum-1])
                        print(sys.exc_info())
                        exit(1)
                    colnum += 1
                insertStatement = insertStatement[:-1];
                insertStatement = insertStatement + ");"
                
                cur.executescript(insertStatement)
                
                if (rownum%500 == 0):
                    print(rownum)
                
                try: 
                    pass#print(insertStatement)
                except:
                    print("error in printing insert")
                    print(sys.exc_info())
                    exit(1)
            rownum += 1
        con.commit()
        
        #test output
        cur.execute("SELECT TOP 10 * FROM Cars")
        rows = cur.fetchall()
        for row in rows:
            print row
        
        
    except lite.Error, e: 
        if con:
            con.rollback()
        print "Error in sql: %s:" % e.args[0]
        print(sys.exc_info())
        print insertStatement
        sys.exit(1)
             
#insertCsvFile('Author')
#checkTables()
createStatementsFile('PaperAuthor')
        